var mysql      = require('mysql');
exports.route=function(path,req,res) {
    var next=eval(path);
    next(req,res);
};

var sql = mysql.createConnection({
    host     : 'localhost',
    user     : 'chzh',
    password : 'chzh5188',
    database : 'blogs'
});
var list=[];
var top=[];
sql.connect();

var getname=function(req,res){
    res.send('陈宗豪');
    res.end();
};
function getbyid(req,res){
    if(req.body.id){
        var sq = 'select * from `index` where `id`='+req.body.id+';';
        //console.log(sq);
        sql.query(sq,function(err,result,fields){
            if(err) throw err;
            if(result){
                var obj=JSON.parse(JSON.stringify(result[0]));
                obj.good=parseInt(obj.good);
                obj.bad=parseInt(obj.bad);
                obj.saw=parseInt(obj.saw);
                obj.comment=parseInt(obj.comment);
                res.json({viewed:obj});
                saw(req,res);
            }
        })
    }
}
function saw(req,res) {
    var user = req.cookies.user;
    checkstatus(user,req.body.id,'saw',res,function(sq){
        var sq = 'insert `saw`(`user`,`blogid`) values(\''+ user+'\','+req.body.id+');';
        //console.log(sq);
        sql.query(sq,function (err,result,fields) {
            if(err) throw err;
            if(result) {
                updatestatus(req.body.id,'saw',res,null);
            }
        })
    })
}
function sendcomment(req,res){
    var user = req.cookies.user;
    var table = 'comment';
    var today=new Date();
    var date=today.toLocaleString();
    if(req.body.id){
        var sq = 'insert `'+table+'` (`user`,`blogid`,`comment`,`date`) values(\''+user+'\','+req.body.id+',\''+req.body.comment+'\',\''+date+'\');';
        //console.log(sq);
        saw(req,res);
        sql.query(sq,function (err,result,fields) {
            if(err) throw err;
            if(result) {
                updatestatus(req.body.id,table,res,'success');
            }
        })
    }

}
function getcomment(req,res){
    //console.log(req.body);
    if(req.body.id){
        var sq= 'select * from `comment` where `blogid` ='+req.body.id+';';
        //console.log(sq);
        sql.query(sq,function(err,result,fields){
            if(err) throw err;
            if(result){
                var comments=[];
                result.forEach(function(item,i){
                    comments.push(JSON.parse(JSON.stringify(item).replace('RowDataPacket','')));
                });
                res.json({comments:comments});
            }
        })
    }

}
function good(req,res){
    var user = req.cookies.user;
    saw(req,res);
    checkstatus(user,req.body.id,'good',res,function(sq){
        var sq = 'insert `good`(`user`,`blogid`) values(\''+ user+'\','+req.body.id+');';
        //console.log(sq);
        sql.query(sq,function (err,result,fields) {
            if(err) throw err;
            if(result) {
                updatestatus(req.body.id,'good',res,'success');
            }
        })
    })
}
function bad(req,res){
    var user = req.cookies.user;
    saw(req,res);
    checkstatus(user,req.body.id,'bad',res,function(sq){
        var sq = 'insert `bad`(`user`,`blogid`) values(\''+user+'\','+req.body.id+');';
        //console.log(sq);
        sql.query(sq,function (err,result,fields) {
            if(err) throw err;
            if(result) {
                updatestatus(req.body.id,'bad',res,'success');
            }
        })
    })
}
function checkstatus(user,id,table,res,callback){
    if(table&&user&&id){
        var sq = 'select `user` from `'+table+'` where `user` = \''+user+'\' and `blogid` = '+id+';';
        //console.log(sq);
        sql.query(sq,function (err,result,fields) {
            if(err) throw err;
            if(result) {
                if(!result[0]){
                    // //console.log('check over');
                    callback();
                }else{
                    // //console.log(result);
                    if(table==='good'||table==='bad'){
                        deletestatus(res,user,id,table);
                    }

                }

            }
        });
    }

}

function updatestatus(id,table,res,mes){
    if(table&&id){
        var sq = 'update `index` set `'+table+'`=( SELECT COUNT(user) FROM `'+table+'` WHERE `blogid` = '+id+') where `id`='+id+';';
        //console.log(sq);
        sql.query(sq,function (err,result,fields) {
            if(err) throw err;
            if(result) {
                // //console.log('success');
                if(mes){
                    res.send(mes);
                }

            }
        });
    }

}
function deletestatus(res,user,id,table){
    if(table&&user&&id){
        var sq = 'delete from `'+table+'` where `user` = \''+user+'\' and `blogid` = '+id+';';
        //console.log(sq);
        sql.query(sq,function (err,result,fields) {
            if(err) throw err;
            if(result) {
                // //console.log('success');
                updatestatus(id,table,res,'delete');

            }
        });
    }

}
function getbytag(req,res){
    if(req.body.tag&&req.body.page){
        gettotal(req.body.tag,function(limit,total){
            gettop(req.body.tag,total,function(total){
            var sq = 'select * from `index` where `tag`=\''+req.body.tag+'\' order by `id` desc '+(req.body.page-1)*5+',5';
            sql.query(sq,function(err,result,field){
                if(err) throw err;
                if(result){
                    list=[];
                    top=[];
                    result.forEach(function(item,i){
                        list.push(JSON.parse(JSON.stringify(item).replace('RowDataPacket','')));
                    });
                    list.forEach(function(item,i){
                        // item.date=item.date.split('T')[0];
                        item.good=parseInt(item.good);
                        item.bad=parseInt(item.bad);
                        item.saw=parseInt(item.saw);
                        item.comment=parseInt(item.comment);
                        if(item.top==='1'){
                            top.push(item);
                            list.slice(i);
                        }
                    });
                    // //console.log(list);
                    res.json({
                        total:total,
                        top:top,
                        list:list
                    })
                }
            })
        })});

    }
}
function gettags(req,res){
    var sq='select DISTINCT(`tag`) as tag from `index`';
    //console.log(sq);
    sql.query(sq,function(err,result,field){
        if(err) throw err;
        if(result) {
            list=[];
            result.forEach(function(item,i){
                if(item.tag&&item.tag!=='null'&&item.tag!==''){
                    list.push(item.tag);
                }

            });
            res.json({tags:list});
        }
    })
}
function getwords(req,res){
    var temp=function(total){
        var first=(req.body.page-1)*5-top.length;
        if(first<0) first=0;
        var last=5;
        if(req.body.page===1){
            last-=top.length
        }
        var sq;
        if(req.body.tag){
            sq='select * from `index` where `top`=0 and`tag`=\''+req.body.tag+'\' order by `id` desc limit '+first+','+last;
        }else{
            sq='SELECT * FROM `index` WHERE `top`=0 ORDER BY `id` DESC LIMIT '+first+','+last;
        }

        //console.log(sq);
        sql.query(sq,function (err,result,fields) {
            if(err) throw err;
            if(result) {
                list=[];
                result.forEach(function(item,i){
                    list.push(JSON.parse(JSON.stringify(item).replace('RowDataPacket','')));
                });
                list.forEach(function(item,i){
                    // item.date=item.date.split('T')[0];
                    item.good=parseInt(item.good);
                    item.bad=parseInt(item.bad);
                    item.saw=parseInt(item.saw);
                    item.comment=parseInt(item.comment);

                });
                // //console.log(list);
                res.json({
                    total:total,
                    top:req.body.page===1?top:[],
                    list:list
                })
            }
        });
    };
    var limit=req.body.tag;
    gettotal(limit,function(limit,total){
        if(req.body.page>1){
            temp(total);
        }else{
            gettop(limit,total,function(total){
                temp(total);
            })
        }

    })
}
function gettop(limit,total,callback){
    var sq='SELECT * FROM `index` WHERE `top`>0 ';
    if(limit){
        var que='AND `tag`=\''+limit+'\'';
        sq+=que;
    }
    sq+=' ORDER BY `top`';
    //console.log(sq);
    sql.query(sq,function (err,result,field) {
        if(err) throw err;
        if(result){
            top=[];
            result.forEach(function(item,i){
                top.push(JSON.parse(JSON.stringify(item).replace('RowDataPacket','')));
            });
            top.forEach(function(item,i){
                // item.date=item.date.split('T')[0];
                item.good=parseInt(item.good);
                item.bad=parseInt(item.bad);
                item.saw=parseInt(item.saw);
                item.comment=parseInt(item.comment);

            });
            if(callback) callback(total);
        }
    })
}
function gettotal(limit,callback){
    var sq='SELECT COUNT(*) AS total FROM `index`';
    if(limit){
        var que='WHERE `tag`=\''+limit+'\'';
        sq+=que;
    }
    //console.log(sq);
    sql.query(sq,function (err,result,fields) {
        if (err) throw err;
        if (result) {
            callback(limit,result[0].total);
        }
    });
}